from datetime import datetime
import pymysql
#pip install pymysql


# 单线程使用的mysql连接
class MySQLConnector:
    def __init__(self, host, port, user, password, database):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.connection = None

    def connect(self):
        try:
            self.connection = pymysql.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database,
                port=self.port,
                cursorclass=pymysql.cursors.DictCursor
            )
            print("Connected to MySQL database")
        except pymysql.Error as e:
            print("Error connecting to MySQL:", e)

    def execute_query(self, query, *args):
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(query, args)
                return cursor.fetchall()
        except pymysql.Error as e:
            print("Error executing query:", e)

    def insert(self, sql_insert, params):
        with self.connection.cursor() as cursor:
            # 开始事务
            self.connection.begin()
            try:
                # 这是个元组对象
                # val_insert = ("value1", "value2")
                val_insert = params
                cursor.execute(sql_insert, val_insert)
                print(cursor.rowcount, "record inserted.")
                # 提交事务
                self.connection.commit()
            except Exception as e:
                # 如果出现错误则回滚事务
                print("Error:", e)
                self.connection.rollback()

    def close_connection(self):
        if self.connection:
            self.connection.close()
            print("MySQL connection closed")


if __name__ == "__main__":
    # 在作为主程序运行时执行以下代码
    # Example usage:
    # 创建MySQLConnector实例
    mysql_conn = MySQLConnector("10.10.44.176", 13306, "root", "root", "py_db");
    # 连接到 MySQL 数据库
    mysql_conn.connect()
    # 执行查询
    result = mysql_conn.execute_query("SELECT * FROM user")

    for row in result:
        print(row)
        print(row["name"])
        print(row["create_time"].strftime("%Y-%m-%d"))

    # 插入数据
    current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    # mysql_conn.insert("insert into user (name, create_time) values (%s, %s)", ("LYH", current_time))

    result = mysql_conn.execute_query("SELECT * FROM user WHERE name = %s", "LYH")
    print(result)
    # 关闭连接
    mysql_conn.close_connection()

